XMLCOMMENT(comment)
XML functions provide functionality for working with XML data. See also the JSONTOXML function.
Returns an xml comment.
XMLCOMMENT(comment)
Comment is a string. Return value is xml.
Returns an XML with the concatination of the given xml types.
XMLCONCAT(content [, content]*)
Content is xml. Return value is xml.
If a value is null, it will be ignored. If all values are null, null is returned.
Returns an XML element with the given name and content.
XMLELEMENT([NAME] name [, <NSP>] [, <ATTR>][, content]*) ATTR:=XMLATTRIBUTES(exp [AS name] [, exp [AS name]]*) NSP:=XMLNAMESPACES((uri AS prefix | DEFAULT uri | NO DEFAULT))+
If the content value is of a type other than xml, it will be escaped when added to the parent element. Null content values are ignored. Whitespace in XML or the string values of the content is preserved, but no whitespace is added between content values.
XMLNAMESPACES is used provide namespace information. NO DEFAULT is equivalent to defining the default namespace to the null uri - xmlns="". Only one DEFAULT or NO DEFAULT namespace item may be specified. The namespace prefixes xmlns and xml are reserved.
If a attribute name is not supplied, the expression must be a column reference, in which case the attribute name will be the column name. Null attribute values are ignored.
Name, prefix are identifiers. uri is a string literal. content can be any type. Return value is xml. The return value is valid for use in places where a document is expected.
with an xml_value of <doc/>
XMLELEMENT(NAME "elem", 1, '<2/>', xml_value)
Returns:
<elem>1<2><doc/><elem/>
Returns an concatination of XML elements for each content item.
XMLFOREST(content [AS name] [, <NSP>] [, content [AS name]]*)
See XMLELEMENT for the definition of NSP - XMLNAMESPACES
Name is an identifier. Content can be any type. Return value is xml.
If a name is not supplied for a content item, the expression must be a column reference, in which case the element name will be a partially escaped version of the column name.
Returns an XML type representation of the string value expression.
XMLPARSE((DOCUMENT|CONTENT) expr [WELLFORMED])
expr in {string, clob, blob}. Return value is xml.
If DOCIMENT is specified then the expression must have a single root element and may or may not contain an XML declaration.
If WELLFORMED is specified then validation is skipped; this is especially useful for CLOB and BLOB known to already be valid.
Returns an xml processing instruction.
XMLPI([NAME] name [, content])
Name is an identifier. Content is a string. Return value is xml.
Returns the XML result from evaluating the given xquery.
XMLQUERY([<NSP>] xquery [<PASSING>] [(NULL|EMPTY) ON EMPTY]] PASSING:=PASSING exp [AS name] [, exp [AS name]]*
See XMLELEMENT for the definition of NSP - XMLNAMESPACES
Namespaces may also be directly declared in the xquery prolog.
The optional PASSING clause is used to provide the context item, which does not have a name, and named global variable values. If the xquery uses a context item and none is provided, then an exception will be raised. Only one context item may be specified and should be an XML type. All non-context non-XML passing values will be converted to an appropriate XML type.
The ON EMPTY clause is used to specify the result when the evaluted sequence is empty. EMPTY ON EMPTY, the default, returns an empty XML result. NULL ON EMPTY returns a null result.
xquery in string. Return value is xml.
XMLQUERY is part of the SQL/XML 2006 specification.
See also FROM Clause#XMLTABLE
See also XQuery Optimization
Returns a character type representation of the xml expression.
XMLSERIALIZE([(DOCUMENT|CONTENT)] xml [AS datatype] [ENCODING enc] [VERSION ver] [(INCLUDING|EXCLUDING) XMLDECLARATION])
Return value matches datatype. If no datatype is specified, then clob will be assumed.
The type may be character (string, varchar, clob) or binary (blob, varbinar). CONTENT is the default. If DOCUMENT is specified and the xml is not a valid document or fragment, then an exception is raised.
The encoding enc is specified as an identifier. A character serialization may not specify an encoding. The version ver is specified as a string literal. If a particular XMLDECLARATION is not specified, then the result will have a declaration only if performing a non UTF-8/UTF-16 or non version 1.0 document serialization or the underlying xml has an declaration. If CONTENT is being serialized, then the declaration will be omitted if the value is not a document or element.
See the following example that produces a BLOB of XML in UTF-16 including the appropriate byte order mark of FE FF and XML declaration.
XMLSERIALIZE(DOCUMENT value AS BLOB ENCODING "UTF-16" INCLUDING XMLDECLARATION)
Applies an XSL stylesheet to the given document.
XSLTRANSFORM(doc, xsl)
Doc, xsl in {string, clob, xml}. Return value is a clob.
If either argument is null, the result is null.
Applies the XPATH expression to the document and returns a string value for the first matching result. For more control over the results and XQuery, use the XMLQUERY function.
XPATHVALUE(doc, xpath)
Doc in {string, clob, blob, xml}. xpath is string. Return value is a string.
Matching a non-text node will still produce a string result, which includes all descendant text nodes. If a single element is matched that is marked with xsi:nil, then null will be returned.
When the input document utilizes namespaces, it is sometimes necessary to specify XPATH that ignores namespaces:
<?xml version="1.0" ?> <ns1:return xmlns:ns1="http://com.test.ws/exampleWebService">Hello<x> World</x></return>
Function:
xpathValue(value, '/*[local-name()="return"]')
Results in 'Hello World'